package com.sky.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
@Mapper
public interface ReportMapper {
    @Select("select sum(amount) from orders where order_time between #{beginTime} and #{endTime} and status = #{status}")
    Double getTurnover(@Param("beginTime") LocalDateTime beginTime,
                                 @Param("endTime") LocalDateTime endTime,
                                 @Param("status") Long status);
    @Select("select count(id) from user where create_time < #{endTime}")
    Integer getTotalUser(@Param("endTime") LocalDateTime endTime);
    @Select("select count(id) from user where create_time between #{beginTime} and #{endTime}")
    Integer getNewUser(@Param("beginTime") LocalDateTime beginTime,@Param("endTime") LocalDateTime endTime);
    @Select("select count(id) from orders where order_time between #{beginTime} and #{endTime}")
    Integer getOrderCount(@Param("beginTime") LocalDateTime beginTime,@Param("endTime") LocalDateTime endTime);
    @Select("select count(id) from orders where order_time between #{beginTime} and #{endTime} and status = #{status}")
    Integer getValidOrderCount(@Param("beginTime") LocalDateTime beginTime,@Param("endTime") LocalDateTime endTime,@Param("status") int status);
    @Select("SELECT od.name name FROM order_detail od, orders o where od.order_id = o.id and o.status = 5 and order_time > #{beginTime}  and order_time < #{endTime}  group by od.name order by sum(od.number) DESC limit 0,10")
    List<String> getSalesTop10Name(@Param("beginTime") LocalDateTime begin,@Param("endTime") LocalDateTime end);
    @Select("SELECT sum(od.number) number FROM order_detail od, orders o where od.order_id = o.id and o.status = 5 and order_time > #{beginTime}  and order_time < #{endTime}  group by od.name order by number DESC limit 0,10")
    List<String> getSalesTop10Number(@Param("beginTime") LocalDateTime begin,@Param("endTime") LocalDateTime end);
}
